<?php

include 'DBconn.php';

class ProductMaster {

	public function getDitinctProName()
	{ 
		$ret = array();
		$result = mysql_query("SELECT distinct pro_name from product_header where activestatus=1") or die(mysql_error());
        while ($row = mysql_fetch_object($result))
		{
		   $temp = new VoProName;
           $temp->pro_name = $row->pro_name;
		   $ret[] = $temp;
		}
	 return $ret;
	}
	
	public function getDitinctPartNo()
	{ 
		$ret = array();
		$result = mysql_query("SELECT distinct part_number from product_header where activestatus=1") or die(mysql_error());
        while ($row = mysql_fetch_object($result))
		{
		   $temp = new VoPartNo;
           $temp->part_number = $row->part_number;
		   $ret[] = $temp;
		}
	 return $ret;
	}
	
	public function getDitinctStampNo()
	{ 
		$ret = array();
		$result = mysql_query("SELECT distinct stamp_number from product_header where activestatus=1") or die(mysql_error());
        while ($row = mysql_fetch_object($result))
		{
		   $temp = new VoStampNo;
           $temp->stamp_number = $row->stamp_number;
		   $ret[] = $temp;
		}
	 return $ret;
	}
	
	public function addNewProduct($VObject,$image) 
	{
		$image_fk = $this->addProductImage($image);
		
        $query = "insert into product_header(pro_name,part_number,model_no,pro_description,markup_type,markcode_serialnumber_fk,stamp_number,pro_oem,qty_minimum,listprice,discount,netprice,default_price,currency_serialnumber,pro_manufacturer,pro_image_fk,pro_sku,bin_no,cre_userid,cre_datetime,activestatus)values('$VObject->pro_name','$VObject->part_number','$VObject->model_no','$VObject->pro_description','$VObject->markup_type','$VObject->markcode_serialnumber_fk','$VObject->stamp_number','$VObject->pro_oem','$VObject->qty_minimum','$VObject->listprice','$VObject->discount','$VObject->netprice','$VObject->default_price','$VObject->currency_serialnumber','$VObject->pro_manufacturer','$image_fk','$VObject->pro_sku','$VObject->bin_no','$VObject->cre_userid','$VObject->cre_datetime',1)";
        $s = mysql_query($query) or die(mysql_error());
        $last_id = mysql_insert_id();

		$query1 = "insert into product_details(pro_serialnumber_fk,currency_serialnumber,markup_type,listprice,discount,markup1,markup2,markup3,markup4,markup5,markup6,default_price,cre_userid,cre_datetime,activestatus)values('$last_id ','$VObject->currency_serialnumber','$VObject->markup_type','$VObject->listprice','$VObject->discount','$VObject->markup1','$VObject->markup2','$VObject->markup3','$VObject->markup4','$VObject->markup5','$VObject->markup6','$VObject->default_price','$VObject->cre_userid','$VObject->cre_datetime',1)";
        $s1 = mysql_query($query1) or die(mysql_error());
		
        $result = mysql_query("select serialnumber from warehouse_header where activestatus=1") or die(mysql_error());
        while ($row = mysql_fetch_object($result)) {
            $query1 = "insert into stock_header(warehouse_serialnumber_fk,pro_serialnumber,on_hand,on_sale,in_order,sold,cre_userid,cre_datetime,activestatus) values('$row->serialnumber','$last_id',0,0,0,0,'$VObject->cre_userid','$VObject->cre_datetime',1)";
            mysql_query($query1) or die(mysql_error());
        }

        //return $this->getProductListByFK($last_id);
    }

    public function deleteProduct($serialnumber) 
	{
        $s = mysql_query("update product_header set activestatus=0 where serialnumber='$serialnumber'") or die(mysql_error());
		$s1 = mysql_query("update product_details set activestatus=0 where pro_serialnumber_fk='$serialnumber'") or die(mysql_error());
        $s2 = mysql_query("update stock_header set activestatus=0 where pro_serialnumber='$serialnumber'") or die(mysql_error());
        if ($s) {
            mysql_query("COMMIT");
        } else {
            mysql_query("ROLLBACK");
        }
        return 'true';
    }

    public function editProduct($VObject,$image) 
	{
		$image_fk = $this->updateProductImage($image,$VObject->pro_image_fk);
		
        $query = "update product_header set pro_name='$VObject->pro_name',part_number='$VObject->part_number',model_no='$VObject->model_no',markup_type='$VObject->markup_type',markcode_serialnumber_fk='$VObject->markcode_serialnumber_fk',stamp_number='$VObject->stamp_number',pro_oem='$VObject->pro_oem',qty_minimum='$VObject->qty_minimum',listprice='$VObject->listprice',discount='$VObject->discount',netprice='$VObject->netprice',default_price='$VObject->default_price',currency_serialnumber='$VObject->currency_serialnumber',pro_manufacturer='$VObject->pro_manufacturer',pro_image_fk='$VObject->pro_image_fk',pro_sku='$VObject->pro_sku',bin_no='$VObject->bin_no',pro_description='$VObject->pro_description' where serialnumber='$VObject->serialnumber'";
        $s = mysql_query($query) or die(mysql_error());
		
		$query1 = "update product_details set activestatus=0 where pro_serialnumber_fk='$VObject->serialnumber'";
        $s1 = mysql_query($query1) or die(mysql_error());
		
		$query1 = "insert into product_details(pro_serialnumber_fk,currency_serialnumber,markup_type,listprice,discount,markup1,markup2,markup3,markup4,markup5,markup6,default_price,cre_userid,cre_datetime,activestatus)values('$VObject->serialnumber','$VObject->currency_serialnumber','$VObject->markup_type','$VObject->listprice','$VObject->discount','$VObject->markup1','$VObject->markup2','$VObject->markup3','$VObject->markup4','$VObject->markup5','$VObject->markup6','$VObject->default_price','$VObject->cre_userid','$VObject->cre_datetime',1)";
        $s1 = mysql_query($query1) or die(mysql_error());
		
        //return $this->getProductListByFK($VObject->serialnumber);
    }
	
    public function addProductImage($image) 
	{
        $query = "insert into pro_image_master(product_image,activestatus)values('$image',1)";
        $s = mysql_query("$query") or die(mysql_error());
        $last_id = mysql_insert_id();
		
        return (int) $last_id;
    }

    public function updateProductImage($image,$pro_image_fk) 
	{
        $query = "update pro_image_master set product_image='$image' where serialnumber='$pro_image_fk' and activestatus=1";
        $s = mysql_query("$query") or die(mysql_error());
    }

    public function deleteProductImage($serialnumber) {
        $query = "update pro_image_master set  activestatus=0 where serialnumber='$serialnumber'";
        $s = mysql_query("$query") or die(mysql_error());
    }

    public function getProductImage($serialnumber) {
        $query = "select * from pro_image_master where serialnumber='$serialnumber' and activestatus=1";
        $result = mysql_query($query);
        $row = mysql_fetch_object($result);

        $temp = new VOProductImage();
        $temp->serialnumber = (int) $row->serialnumber;
        $temp->product_image = $row->product_image;
        $temp->activestatus = (int) $row->activestatus;

        mysql_free_result($result);
        return $temp;
    }

    public function getProductList() {
        $result = mysql_query("SELECT * from product_header where activestatus=1") or die(mysql_error());
        $ret = array();
        while ($row = mysql_fetch_object($result)) {
            $temp = new VOProductList();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->pro_description = $row->pro_description;
            $temp->stamp_number = $row->stamp_number;
			$temp->model_no = $row->model_no;
            $temp->isSelected = false;
			
			$result1 = mysql_query("SELECT currency_value from currency_detail WHERE activestatus ='1'
					   and to_currency_fk='$row->currency_serialnumber' ORDER BY detserialnumber DESC LIMIT 1") or die(mysql_error());
			$row1 = mysql_fetch_row($result1);
			$currency_value =(double)$row1[0];
			
			$listPrice = (double)$row->listprice;			
			$discount = (double)$row->discount;	
			$temp->sgd_listprice = round((($listPrice - ($listPrice * $discount/100))* $currency_value),4);
	
            $ret[] = $temp;
        }
        mysql_free_result($result);
        return $ret;
    }

    public function getProductListOnSearch() {
        $result = mysql_query("SELECT * from product_header where activestatus=1") or die(mysql_error());
        $ret = array();
        while ($row = mysql_fetch_object($result)) {
            $temp = new VOProductSearch;
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->model_no = $row->model_no;
            $temp->stamp_number = $row->stamp_number;
			$temp->pro_description = $row->pro_description;
            $temp->pro_oem = $row->pro_oem;
            $temp->markup_type = $row->markup_type;
            $temp->markcode_serialnumber_fk = (int) $row->markcode_serialnumber_fk;
            $temp->qty_minimum = (double) $row->qty_minimum;
            $temp->listprice = (double) $row->listprice;
            $temp->discount = (double) $row->discount;
            $temp->netprice = (double) $row->netprice;
            $temp->default_price = $row->default_price;
            $temp->currency_serialnumber = (int) $row->currency_serialnumber;
            $temp->pro_manufacturer = $row->pro_manufacturer;
            $temp->pro_image_fk = (int) $row->pro_image_fk;
            $temp->pro_sku = $row->pro_sku;
            $temp->cre_userid = (int) $row->cre_userid;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			
			$pro_serialnumber=$row->serialnumber;
			
			$query1 = "select Sum(on_hand) as 'on_hand',Sum(on_sale) as 'on_sale',Sum(in_order) as 'in_order' from stock_header where 
			pro_serialnumber='$pro_serialnumber' and activestatus=1";
			$result1 = mysql_query($query1) or die(mysql_error());
			$row1 = mysql_fetch_object($result1);
            $temp->on_hand = (int) $row1->on_hand;
            $temp->in_order = (int) $row1->in_order;
            $temp->on_sale = (int) $row1->on_sale;
			
			$compdate = date('Y-m-d', strtotime(date("Y-m-d", strtotime(date("Y-m-d"))) . " -30 day"));
			$query2 = "Select IFNULL(SUM(delivered_qty),0) as 'sold' From delivery_order_pro_details where 
			pro_serialnumber_fk='$pro_serialnumber' and cre_datetime > '$compdate' and activestatus=1";
			$result2 = mysql_query($query2) or die(mysql_error());
			$row2 = mysql_fetch_object($result2);
            $temp->sold = (int) $row2->sold;
			
            $ret[] = $temp;
        }
        mysql_free_result($result);
        return $ret;
    }

    public function getProductListWithPrice() {
        $result = mysql_query("Select ph.serialnumber,ph.pro_name,ph.pro_description,ph.part_number,ph.stamp_number,ph.model_no,
		ph.currency_serialnumber,ph.listprice,ph.discount,ph.netprice,ph.default_price,mh.markup_type,mh.priceA,mh.priceB,mh.priceC,mh.priceD,mh.priceE,
		mh.priceF from product_header as ph LEFT JOIN markup_header as mh ON mh.activestatus=1 and mh.serialnumber=ph.markcode_serialnumber_fk 
		where ph.activestatus=1") or die(mysql_error());
        $ret = array();
        while ($row = mysql_fetch_object($result)) {
            $temp = new VOProductListWithPrice();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->stamp_number = $row->stamp_number;
			$temp->model_no = $row->model_no;
			$temp->listprice = (double) $row->listprice;
			$temp->pro_description = $row->pro_description;

			$result1 = mysql_query("SELECT * from product_details where activestatus=1 and pro_serialnumber_fk='$row->serialnumber' ORDER BY pro_detserialnumber DESC LIMIT 1") or die(mysql_error());
			$row1 = mysql_fetch_object($result1);
			if(mysql_num_rows($result1) > 0)
			{
				$temp->pro_detserialnumber=(int) $row1->pro_detserialnumber;
			}

			$netprice = (double)$row->netprice;
			
			$m1 = (double)$row->priceA;
			$m2 = (double)$row->priceB;
			$m3 = (double)$row->priceC;
			$m4 = (double)$row->priceD;
			$m5 = (double)$row->priceE;
			$m6 = (double)$row->priceF;
			
			$result1 = mysql_query("SELECT detserialnumber,currency_value from currency_detail WHERE activestatus ='1'
					   and to_currency_fk='$row->currency_serialnumber' ORDER BY detserialnumber DESC LIMIT 1") or die(mysql_error());
			$row1 = mysql_fetch_row($result1);
			$temp->currency_detserial =(int)$row1[0];
			$currency_value =(double)$row1[1];
			
			$costPrice = $netprice * $currency_value;
			
			$listPrice = (double)$row->listprice;			
			$discount = (double)$row->discount;	
			$temp->sgd_listprice = round((($listPrice - ($listPrice * $discount/100))* $currency_value),4);
			
			$price1=0;
			$price2=0;
			$price3=0;
			$price4=0;
			$price5=0;
			$price6=0;
			
			if($row->markup_type!='Net Markup')
			 {
				$price1 = round(($costPrice/(1-($m1/100))),4);
				$price2 = round(($costPrice/(1-($m2/100))),4);
				$price3 = round(($costPrice/(1-($m3/100))),4);
				$price4 = round(($costPrice/(1-($m4/100))),4);
				$price5 = round(($costPrice/(1-($m5/100))),4);
				$price6 = round(($costPrice/(1-($m6/100))),4);
			 }else{
				$price1 = round(($costPrice*(1+($m1/100))),4);
				$price2 = round(($costPrice*(1+($m2/100))),4);
				$price3 = round(($costPrice*(1+($m3/100))),4);
				$price4 = round(($costPrice*(1+($m4/100))),4);
				$price5 = round(($costPrice*(1+($m5/100))),4);
				$price6 = round(($costPrice*(1+($m6/100))),4);
			 }
			
            $priceList = array();

            $priceObj1 = new VOPrice();
            $priceObj1->sgd_price = (double)$price1;
			$priceObj1->other_price = (double)$price1;
            $priceObj1->hilight = 0;
            if ($row->default_price == 'Price A') {
                $priceObj1->hilight = 1;
                $temp->unitPrice = (double)$price1;
            }
            $priceList[] = $priceObj1;

            $priceObj2 = new VOPrice();
            $priceObj2->sgd_price = (double)$price2;
			$priceObj2->other_price = (double)$price2;
            $priceObj2->hilight = 0;
            if ($row->default_price == 'Price B') {
                $priceObj2->hilight = 1;
                $temp->unitPrice = (double)$price2;
            }
            $priceList[] = $priceObj2;

            $priceObj3 = new VOPrice();
            $priceObj3->sgd_price = (double)$price3;
			$priceObj3->other_price = (double)$price3;
            $priceObj3->hilight = 0;
            if ($row->default_price == 'Price C') {
                $priceObj3->hilight = 1;
                $temp->unitPrice = (double)$price3;
            }
            $priceList[] = $priceObj3;

            $priceObj4 = new VOPrice();
            $priceObj4->sgd_price = (double)$price4;
			$priceObj4->other_price = (double)$price4;
            $priceObj4->hilight = 0;
            if ($row->default_price == 'Price D') {
                $priceObj4->hilight = 1;
                $temp->unitPrice = (double)$price4;
            }
            $priceList[] = $priceObj4;

            $priceObj5 = new VOPrice();
            $priceObj5->sgd_price = (double)$price5;
			$priceObj5->other_price = (double)$price5;
            $priceObj5->hilight = 0;
            if ($row->default_price == 'Price E') {
                $priceObj5->hilight = 1;
                $temp->unitPrice = (double)$price5;
            }
            $priceList[] = $priceObj5;

            $priceObj6 = new VOPrice();
            $priceObj6->sgd_price = (double)$price6;
			$priceObj6->other_price = (double)$price6;
            $priceObj6->hilight = 0;
            if ($row->default_price == 'Price F') {
                $priceObj6->hilight = 1;
                $temp->unitPrice = (double)$price6;
            }
            $priceList[] = $priceObj6;

            $temp->price_list = $priceList;
            $temp->default_price = $row->default_price;
			
			$resultH = mysql_query("SELECT on_hand from stock_header where activestatus=1 and pro_serialnumber='$temp->serialnumber'") or die(mysql_error());
			$on_hand_stock=mysql_fetch_array($resultH);
			$temp->on_hand= (int)$on_hand_stock['on_hand'];
			
            $temp->isSelected = false;
            $ret[] = $temp;
        }
        mysql_free_result($result);
        return $ret;
    }

    public function getProductListByFK($serialnumber) {
        $result = mysql_query("SELECT serialnumber,pro_name,pro_description,part_number,model_no,listprice
		from product_header where serialnumber='$serialnumber' and activestatus=1") or die(mysql_error());
        $row = mysql_fetch_object($result);
        $temp = new VOProductList();
        $temp->serialnumber = (int) $row->serialnumber;
        $temp->pro_name = $row->pro_name;
        $temp->part_number = $row->part_number;
        $temp->pro_description = $row->pro_description;
		$temp->model_no = $row->model_no;
		$temp->listprice = (double)$row->listprice;
        $temp->isSelected = false;
        mysql_free_result($result);
        return $temp;
    }

    public function getAllProduct() {
		$ret = array();
        $result = mysql_query("SELECT * from product_header where activestatus=1") or die(mysql_error());
        while ($row = mysql_fetch_object($result)) {
            $temp = new VOProductMaster();
            $temp->serialnumber = (int) $row->serialnumber;
            $temp->pro_name = $row->pro_name;
            $temp->part_number = $row->part_number;
            $temp->model_no = $row->model_no;
            $temp->stamp_number = $row->stamp_number;
			$temp->pro_description = $row->pro_description;
            $temp->pro_oem = $row->pro_oem;
            $temp->markup_type = $row->markup_type;
            $temp->markcode_serialnumber_fk = (int) $row->markcode_serialnumber_fk;
            $temp->qty_minimum = (double) $row->qty_minimum;
            $temp->listprice = (double) $row->listprice;
            $temp->discount = (double) $row->discount;
            $temp->netprice = (double) $row->netprice;
            $temp->default_price = $row->default_price;
            $temp->currency_serialnumber = (int) $row->currency_serialnumber;
			//$res=mysql_query("select currency_name from currency_header where serialnumber='$row->currency_serialnumber' ");
			//$res1=mysql_fetch_array($res);
			//$temp->$res1['currency_name'];
            $temp->pro_manufacturer = $row->pro_manufacturer;
            $temp->pro_image_fk = (int) $row->pro_image_fk;
            $temp->pro_sku = $row->pro_sku;
			$temp->bin_no = $row->bin_no;
            $temp->cre_userid = (int) $row->cre_userid;
            $temp->cre_datetime = $row->cre_datetime;
            $temp->activestatus = (boolean) $row->activestatus;
			
			$result1 = mysql_query("SELECT * from product_details where activestatus=1 and pro_serialnumber_fk='$row->serialnumber' ORDER BY pro_detserialnumber DESC LIMIT 1") or die(mysql_error());
			$row1 = mysql_fetch_object($result1);
			if(mysql_num_rows($result1) > 0)
			{
				$temp->pro_detserialnumber=(int) $row1->pro_detserialnumber;
				$temp->markup1=(double)$row1->markup1;
				$temp->markup2=(double)$row1->markup2;
				$temp->markup3=(double)$row1->markup3;
				$temp->markup4=(double)$row1->markup4;
				$temp->markup5=(double)$row1->markup5;
				$temp->markup6=(double)$row1->markup6;
			}
			
            $ret[] = $temp;
        }
        mysql_free_result($result);
        return $ret;
    }
}

class VOProductImage {
    var $serialnumber;
    var $product_image;
    var $activestatus;
}

class VOProductListWithPrice {
    var $serialnumber;
    var $pro_name;
	var $pro_description;
    var $part_number;
    var $stamp_number;
	var $model_no;
	var $sgd_listprice;
	var $unitPrice;
	var $listprice;
    var $price_list;
    var $default_price;
	var $currency_detserial;
	var $pro_detserialnumber;
	var $on_hand;
    var $isSelected;
}

class VOPrice {
    var $sgd_price;
	var $other_price;
    var $hilight;
}

class VOProductList {
    var $serialnumber;
    var $pro_name;
    var $part_number;
    var $pro_description;
    var $stamp_number;
	var $model_no;
    var $sgd_listprice;
    var $isSelected;
}

class VOProductSearch {
    var $serialnumber;
    var $pro_name;
    var $part_number;
    var $model_no;
    var $stamp_number;
	var $pro_description;
    var $pro_oem;
    var $markup_type;
    var $markcode_serialnumber_fk;
    var $qty_minimum;
    var $listprice;
    var $discount;
    var $netprice;
    var $default_price;
    var $currency_serialnumber;
    var $pro_manufacturer;
    var $pro_image_fk;
    var $pro_sku;
    var $cre_userid;
    var $cre_datetime;
    var $activestatus;
	var $on_hand;
    var $in_order;
    var $on_sale;
    var $sold;
}

class VOProductMaster {
    var $serialnumber;
    var $pro_name;
    var $part_number;
    var $model_no;
    var $stamp_number;
	var $pro_description;
    var $pro_oem;
    var $markup_type;
    var $markcode_serialnumber_fk;
    var $qty_minimum;
    var $listprice;
    var $discount;
    var $netprice;
    var $default_price;
    var $currency_serialnumber;
    var $pro_manufacturer;
    var $pro_image_fk;
    var $pro_sku;
	var $bin_no;
	var $pro_detserialnumber;
	var $markup1;
	var $markup2;
	var $markup3;
	var $markup4;
	var $markup5;
	var $markup6;
    var $cre_userid;
    var $cre_datetime;
    var $activestatus;
	var $currency_name;
}

class VoProName
{var $pro_name;}

class VoPartNo
{var $part_number;}

class VoStampNo
{var $stamp_number;}

?>